package tgc.edu.tms.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import tgc.edu.tms.custom.CommonRepository;
import tgc.edu.tms.entity.FlowTable;
import tgc.edu.tms.entity.WorkSheetCount;

@Repository
public interface FlowTableRepository extends CommonRepository<FlowTable, Integer> {

	public List<FlowTable> findByTimeAndTeacher(String data, String name);

	public List<FlowTable> findByTimeAndJcAndTeacher(String week, String jc, String teacher);

	public List<FlowTable> findByTimeAndJcAndClassRoom(String week, String section, String classroom);

	public List<FlowTable> findByTimeAndJc(String time, String section);

	public FlowTable findByTimeAndJcAndClasses(String repairTime, String jc, String tbClass);

	// public FlowTable findByTimeAndJcAndTeacher(String repairTime, String jc,
	// String teacher);

	public List<FlowTable> findByTime(String suspendTime);

	public List<FlowTable> findByTimeAndLdAndLc(String time, String ld, String lc);

	public List<FlowTable> findByTeacherAndTime(String name, String time);

	public List<FlowTable> findByTeacherAndTimeAndSknr(String name, String time, String sknr);

	@Query(value = "SELECT DISTINCT teacher FROM flow_table where sknr!=''", nativeQuery = true)
	public List<String> findAllTeacher();

	@Query(value = "SELECT DISTINCT course FROM flow_table where teacher = ?1 and sknr!='' ", nativeQuery = true)
	public List<String> findAllCourse(String teacher);

	public FlowTable findByJcAndTimeAndClassRoom(String jcsj, String xq, String classroom);

	public FlowTable findByJcAndTimeAndTeacher(String jcsj, String xq, String name);

	public FlowTable findByJcAndTimeAndClasses(String jc, String weekDay, String tbClass);

	@Query(value = "SELECT DISTINCT time FROM flow_table where sknr!='' ", nativeQuery = true)
	public List<String> findAllYear();

	@Query(value = "SELECT DISTINCT term FROM flow_table where time like %" + "?1"
			+ "% and sknr!='' ", nativeQuery = true)
	public List<String> findAllTerm(String year);
	public FlowTable findByJcAndTimeAndTeacherAndYear(String jc, String repairTime, String teacher, String year);

	public FlowTable findByTimeAndJcAndClassesAndYear(String repairTime, String jc, String tbClass, String year);

	public FlowTable findByJcAndTimeAndClassesAndYear(String jc, String repairTime, String tbClass, String year);

	@Query(value = "from FlowTable t where t.classes like %?1% and time >= ?2 and time <= ?3 and year = ?4")
	public List<FlowTable> findBybjsj(String string, String string2, String string3, String string4);

	@Query(value = "SELECT DISTINCT teacher FROM flow_table", nativeQuery = true)
	public List<String> findTeacher();

	@Query(value = "SELECT t.teacher,t.course,t.classes,t.zxs,t.ydrs,t.xmh,COUNT(t.id) FROM flow_table t WHERE !ISNULL(t.sknr)AND t.time like %"
			+ "?2" + "% and t.teacher =?1 GROUP BY t.teacher,t.course,t.classes,t.zxs", nativeQuery = true)
	public List<Object[]> findCout(String time, String teacher);
	
	@Query(value = "SELECT t.teacher,t.course,t.classes,t.zxs,t.ydrs,t.xmh,COUNT(t.id) FROM flow_table t WHERE !ISNULL(t.sknr)AND t.term =?1 and t.teacher =?2 GROUP BY t.teacher,t.course,t.classes,t.zxs", nativeQuery = true)
	public List<Object[]> findTerm(String term, String teacher);

}
